using System;
using System.Collections.Generic;
using System.Text;
using student_factory.Metier;
using System.Data.SqlClient;

namespace student_factory.DAO
{
    class ClientDAO
    {
        private static String connection = "data source=localhost;initial catalog=STUDENTS_FACTORY;User ID=sa;Password=";

        public static Client getClient(string id_c)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            Client client = null;
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();

                commande.CommandText = "SELECT * from CLIENTS WHERE ID_C LIKE '"+id_c+"'";
                
                reader = commande.ExecuteReader();

               

                while (reader.Read())
                {
                        client = new Client(id_c, reader["CLI_RAISON_SOCIAL"].ToString());
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return client;
        }


        public static IList<Client> getClients()
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Client> clients = new List<Client>();
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();

                commande.CommandText = "SELECT * from CLIENTS";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    clients.Add(new Client( reader["ID_C"].ToString(), reader["CLI_RAISON_SOCIAL"].ToString()) );
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return clients;
        }

        public static bool addClient(string raisonSociale,out string id_c)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            bool retour = true;
            id_c = "error";
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;
                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "INSERT INTO CLIENTS ( CLI_RAISON_SOCIAL ) VALUES ( '" + raisonSociale + "' )";

                int modif = commande.ExecuteNonQuery();

                commande.CommandText = "SELECT ID_C FROM CLIENTS WHERE CLI_RAISON_SOCIAL LIKE '" + raisonSociale + "'";

                reader = commande.ExecuteReader();
                while (reader.Read())
                {
                   id_c = reader["ID_C"].ToString();
                }

                if (modif == 0)
                    retour = false;

            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (connexion != null)
                    connexion.Close();
            }

            return retour;
        }
    }
}
